package io.renren.common.utils.excel;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.List;
import java.util.Map;

/**
 * @description: 导出模板填入级联下拉框工具类
 * @author: wurj
 * @Date: 2022/2/15 23:58:58
 */
@Slf4j
public class ExcelTemplateUtils {

    /**
     * 初始化公式“名称”
     *
     * @param workbook
     * @param wsSheetName
     * @param firstQuantity
     */
    public static void initWarehouseNameMapping(HSSFWorkbook workbook, String wsSheetName, String firstName, int firstQuantity) {
        Name name = workbook.createName();
        // 设置仓库名称
        name.setNameName(firstName);
        name.setRefersToFormula(wsSheetName + "!$A$1:$A$" + firstQuantity);
    }

    /**
     * 生成下拉框及提示
     *
     * @param formulaString
     * @param firstRow      firstRow
     * @param columnIndex
     * @return
     */
    public static HSSFDataValidation getDataValidationByFormula(String formulaString, int firstRow, int columnIndex) {
        // 生成下拉列表-服务类型
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, 6000, columnIndex, columnIndex);
        //  生成下拉框内容
        DVConstraint dvConstraint = DVConstraint.createFormulaListConstraint(formulaString);
        HSSFDataValidation dataValidation = new HSSFDataValidation(cellRangeAddressList, dvConstraint);
        return dataValidation;
    }

    /**
     * 在隐藏sheet中写入库房下拉框数据
     *
     * @param workbook
     * @param wsSheet
     * @param firstList
     */
    public static void writeFirstList(HSSFWorkbook workbook, HSSFSheet wsSheet, List<String> firstList) {
        writeCellList(workbook, wsSheet, firstList, 0);
    }

    /**
     * 在隐藏sheet中写入库房下拉框数据
     *
     * @param workbook
     * @param wsSheet
     * @param list
     * @param cellNum  第几列列
     */
    public static void writeCellList(HSSFWorkbook workbook, HSSFSheet wsSheet, List<String> list, int cellNum) {
        for (int i = 0; i < list.size(); i++) {
            int rowCount = wsSheet.getLastRowNum();
            HSSFRow row;
            if (i <= rowCount && wsSheet.getRow(0) != null) {
                row = wsSheet.getRow(i);
            } else {
                row = wsSheet.createRow(i);
            }
            HSSFCell cell = row.createCell(cellNum);
            cell.setCellValue(list.get(i));
        }
    }

    /**
     * 在隐藏sheet中写入货架下拉框数据
     *
     * @param workbook
     * @param wsSheet
     * @param firstList
     * @param firstAndSecondMap
     */
    public static void writeShelves(HSSFWorkbook workbook, HSSFSheet wsSheet, List<String> firstList, Map<String, List<String>> firstAndSecondMap) {
        for (int i = 0; i < firstList.size(); i++) {
            int referColNum = i + 1;
            String warehouseName = firstList.get(i);
            List<String> shelves = firstAndSecondMap.get(warehouseName);
            if (CollectionUtils.isNotEmpty(shelves)) {
                int rowCount = wsSheet.getLastRowNum();
                if (rowCount == 0 && wsSheet.getRow(0) == null) {
                    wsSheet.createRow(0);
                }
                for (int j = 0; j < shelves.size(); j++) {
                    //前面创建过的行，直接获取行，创建列
                    if (j <= rowCount) {
                        //设置对应单元格的值
                        wsSheet.getRow(j).createCell(referColNum).setCellValue(shelves.get(j));
                    } else {
                        //未创建过的行，直接创建行、创建列
                        //设置每列的列宽
                        wsSheet.setColumnWidth(j, 4000);
                        //创建行、创建列
                        //设置对应单元格的值
                        wsSheet.createRow(j).createCell(referColNum).setCellValue(shelves.get(j));
                    }
                }
            }
            int shelfQuantity = null == shelves || shelves.size() <= 0 ? 1 : shelves.size();
            initShelfNameMapping(workbook, wsSheet.getSheetName(), warehouseName, referColNum, shelfQuantity);
        }
    }

    /**
     * 货架与库房下拉选择数据关联
     *
     * @param workbook
     * @param wsSheetName
     * @param warehouseName 公式名称
     * @param referColNum   数据放于第几列
     * @param shelfQuantity 数据总个数
     */
    public static void initShelfNameMapping(HSSFWorkbook workbook, String wsSheetName, String warehouseName, int referColNum, int shelfQuantity) {
        Name name = workbook.createName();
        // 设置货架名称
        name.setNameName(warehouseName);
        String referColName = getColumnName(referColNum);
        shelfQuantity = shelfQuantity <= 0 ? 1 : shelfQuantity;
        String formula = wsSheetName + "!$" + referColName + "$1:$" + referColName + "$" + shelfQuantity;
        name.setRefersToFormula(formula);
    }

    /**
     * 根据数据值确定单元格位置（比如：0-A, 27-AB）
     *
     * @param index
     * @return
     */
    private static String getColumnName(int index) {
        StringBuilder s = new StringBuilder();
        while (index >= 26) {
            s.insert(0, (char) ('A' + index % 26));
            index = index / 26 - 1;
        }
        s.insert(0, (char) ('A' + index));
        return s.toString();
    }
}
